﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SKDB_Matcher.SQL.Database
{
    class CDatabaseHelper
    {
        private static String SERVER_NAME = "dawork";
        private static String DB_PATH = "SQLEXPRESS";
        private static String DB_USER = "";
        private static String DB_PASS = "";

        static public String Path
        {
            get
            {
                return DB_PATH;
            }
            set
            {
                DB_PATH = value;
            }
        }

        static public String Username
        {
            get
            {
                return DB_USER;
            }
            set
            {
                DB_USER = value;
            }
        }

        static public String Password
        {
            get
            {
                return DB_PASS;
            }
            set
            {
                DB_PASS = value;
            }
        }

        static public String ServerName
        {
            get
            {
                return SERVER_NAME;
            }
            set
            {
                SERVER_NAME = value;
            }
        }


        public static DataSet LoadSKDB_SPORTDataSet()
        {
            return new SKDB_SPORTSDataSet(); 
        }

        public static Boolean CheckConnection(String Server, String Path, String User, String Pass)
        {
            Boolean result = false;
            String connectionString = null;
            Boolean withLogin = false;
            if (User != null )
                if (User.Length > 0)
                    withLogin = true;
            if (withLogin)
                connectionString = "user id=" + User + ";password=" + Pass + ";Integrated Security=no;" + "Data Source=" + Server + "\\" + Path;
            else
                connectionString = "Integrated Security=yes;" + "Data Source=" + Server + "\\" + Path;

            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                con.Close();
                result = true;
            }
            catch (Exception e)
            {
                result = false;
            }

            return result;
        }

        public static Boolean CheckDatabaseConnection(String _databasename)
        {
            Boolean result = false;
            String sConnectionString = "Data Source=" + SERVER_NAME + "\\" + DB_PATH + ";Initial Catalog='" + _databasename + "';Integrated Security=True";
            SqlConnection con = new SqlConnection(sConnectionString);
            try
            {
                con.Open();
                con.Close();
                result = true;
            }
            catch (Exception e)
            {
                result = false;
            }

            return result;
        }

        public static DataSet LoadDataSet(String _databasename)
        {
            CConsole.WriteLine("Loading Database: " + _databasename);
            DataSet result = null;
            String sConnectionString;

            
            if (DB_USER.Length > 0)
                sConnectionString = "user id=" + DB_USER + ";password=" + DB_PASS + ";Data Source=" + SERVER_NAME + "\\" + DB_PATH + ";Initial Catalog='" + _databasename + "';Integrated Security=no";
            else
                sConnectionString = "Data Source=" + SERVER_NAME + "\\" + DB_PATH + ";Initial Catalog='" + _databasename + "';Integrated Security=yes";
            
            result = new DataSet(_databasename);
            SqlConnection con = new SqlConnection(sConnectionString);
            CConsole.WriteLine("Connecting");
            con.Open();
            CConsole.WriteLine("Loading tables");
            DataTable tables = con.GetSchema("Tables");
            foreach (DataRow row in tables.Rows)
            {
                if (row[2].ToString() != "sysdiagrams")
                {
                    SqlDataAdapter daAuthors = new SqlDataAdapter("Select * From " + row[2], con);
                    daAuthors.FillSchema(result, SchemaType.Mapped, row[2].ToString());
                    daAuthors.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    daAuthors.Fill(result, row[2].ToString());
                }
            }
            

            SqlCommand cmd = con.CreateCommand();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT  keys.name, tabs_1.name, keycols.parent_column_id, tabs_2.name, keycols.referenced_column_id FROM sys.foreign_key_columns keycols JOIN sys.foreign_keys keys ON keycols.constraint_object_id = keys.object_id	JOIN sys.tables tabs_1 ON keycols.parent_object_id = tabs_1.object_id JOIN sys.tables tabs_2 ON keycols.referenced_object_id = tabs_2.object_id";
            CConsole.WriteLine("Loading relations");
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                //CConsole.WriteLine((String)reader[0]);
                DataColumn parentCol = result.Tables[(String)reader[1]].Columns[(int)reader[2] - 1];
                DataColumn refCol = result.Tables[(String)reader[3]].Columns[(int)reader[4] - 1];
                DataRelation newRelation = new DataRelation((String)reader[0], refCol, parentCol);
                result.Relations.Add(newRelation);
            }

            

            reader.Close();
            con.Close();
            CConsole.WriteLine("Loading Database finished");
            return result;
        }

        public static void setDataBaseTree(DataSet _source, TreeView _tvDestination)
        {
            int T = 0;
            int C = 0;
            foreach (DataTable table in _source.Tables)
            {
                TreeNode tableNode = new TreeNode(table.TableName);
                tableNode.Tag = T;
                tableNode.ImageIndex = 0;
                tableNode.SelectedImageIndex = 0;
                C = 0;
                foreach (DataColumn column in table.Columns)
                {
                    TreeNode columnNode = new TreeNode(column.Caption + " (" + column.DataType.Name + ")");
                    columnNode.Tag = C;
                    columnNode.ImageIndex = 1;
                    columnNode.SelectedImageIndex = 1;
                    tableNode.Nodes.Add(columnNode);
                    C++;
                }
                _tvDestination.Nodes.Add(tableNode);
                T++;
            }
        }
    }
}
